damircudic/Getty Images
- Pivot tables in Google Sheets summarize smaller sets of data from a more extensive table and group sums, averages, or other statistical measurements in a specific and meaningful way.
- You can create pivot tables in Google Sheets manually or automatically and sort your spreadsheet's data by rows, columns, values, and filters.
- Pivot tables also let you group data manually or automatically to give you a more specific and customized window into your more extensive data set.
- Visit Insider's Tech Reference library for more stories.
Pivot tables in Google Sheets are a way to get a more focused snapshot of your larger database.
They can be handy for their versatility – pivot tables group and filter information according to variables that you pick – and be as simple or complex as you want.
More than just counting, pivot tables can total up amounts, calculate averages, and even help you find errors in your dataset.
Here's how to perform some of the most basic functions of a pivot table.
More ways to edit and work with pivot tables
Here are a few more ways to adjust and get the most out of the pivot table you've created using other facets of the feature in Google Sheets.
- Add data: On the right side where you set up the pivot table, you can always add to Rows, Columns, or Values to expand the pivot table with another variable.
- Change row or column names: To change row or column names, double click inside the label cell and rename it.
- Change sort order: To see your data in a different order, click the dropdown menu within a variable and sort differently or see a separate order (descending or ascending alphabetically, by amount, etc.)
- Change the data range: The data range signifies which cells are being used from the original spreadsheet to make the pivot table. Change the range by clicking the grid symbol at the top of the pivot table editor and editing it.
- Delete data: Any variable you add to your pivot table can be removed using the X feature at the top right of the table.
- Hide data with filters: Filtering allows you to remove specific data from the pivot table. Under Filters, click Add and choose which category you'd like to filter within. Then, under Status, you can use the dropdown menus to uncheck certain conditions or values. For example, you could select to filter by Account number in the budget and deselect a particular bank account number, which would prevent those entries from being counted in the pivot table.
- Move data: Make rows columns and vice versa by simply dragging their boxes to the section you'd like them in - for example, you might see that there are more columns than rows and switch them to make the pivot table more vertically readable.
- Repeat row labels: Not every entry within a group will have a row label. In the box for that group, check Repeat row labels to apply the group name to every row.
- Show totals: Sum totals for each category or group will typically be visible, and you can check or uncheck Show totals within a variable's box to change this.
- Show values as percentages: Change values to percentages by, underneath Values, clicking the dropdown menu under Show as, which will let you convert values into percentages of rows, columns, or totals.
- Create custom formulas: If you want to create a column that does a certain calculation for each row, click Add underneath Values and click Calculated field, which will add a column with no values. Define the formula (let's say you want to subtract 100 from each row value) in the text field and customize how the values are shown in the dropdown menus. Under Summarize by change Sum to Custom.
- Show details of data: Double click any value to show its source data (for example, anything within the "Freelance" category of the budget). This will create a new sheet with a list of the data used to arrive at the value.